package performance;

import org.tinygroup.logger.LogLevel;
import org.tinygroup.threadgroup.AbstractProcessor;
import org.tinygroup.threadgroup.MultiThreadProcessor;

import java.sql.*;
import java.util.concurrent.atomic.AtomicInteger;

/**
 * 清除TPS的测试数据
 * 
 * @author yancheng11334
 * 
 */
public class TpsDeleteData extends BaseTpsTest {

	private static String sql = "select count(1) from score";

	private static String min_sql = "select min(id) from score";


	private static int DELETE_THREAD_NUM = 10;
	private static int DELETE_SQL_NUM = 10000;

	private static long PRINT_SLEEP_TIME = 10000;

	private static final AtomicInteger deleteCount = new AtomicInteger(0);
	private static long startId = 0;

	private static volatile long startTime = System.currentTimeMillis();
	private static volatile long  endTime = 0;
	private static boolean autoCommit = false;
	private static boolean deleteByStatement = false;

	private static int countNum = 0;
	/**
	 * 测试一定时间内的删除SQL性能<br/>
	 * 随机生成主键ID，可以多个客户端运行
	 * @param args
	 * @throws Exception
	 */
	public static void main(String[] args) throws Exception {
		if(args!=null && args.length>0){
			if(args.length>=1){
				DELETE_THREAD_NUM = Integer.parseInt(args[0]);
			}
			if(args.length>=2){
				DELETE_SQL_NUM = Integer.parseInt(args[1]);
			}
			if(args.length>=3){
				deleteByStatement = Boolean.parseBoolean(args[2]);
			}
			if(args.length>=4){
				autoCommit = Boolean.parseBoolean(args[3]);
			}
		}
		logger.logMessage(LogLevel.INFO, "客户端[{}]运行参数:删除线程个数:{},计划完成SQL次数:{},删除方式:{},是否自动提交:{}",System.getProperty("user.name"),DELETE_THREAD_NUM,DELETE_SQL_NUM,deleteByStatement?"Statement":"PreparedStatement",autoCommit?"是":"否");
		init();
		MultiThreadProcessor processors = new MultiThreadProcessor("dbrouter");
		for (int i = 0; i < DELETE_THREAD_NUM; i++) {
			processors.addProcessor(new DeleteThread("delete-" + i));
		}
		processors.addProcessor(new PrintThread("print"));

		startTime = System.currentTimeMillis();
		processors.start();

		long costtime = endTime - startTime;
		logger.logMessage(LogLevel.INFO, "客户端[{}]运行参数:删除线程个数:{},计划完成SQL次数:{},删除方式:{},是否自动提交:{}",System.getProperty("user.name"),DELETE_THREAD_NUM,DELETE_SQL_NUM,deleteByStatement?"Statement":"PreparedStatement",autoCommit?"是":"否");
		logger.logMessage(LogLevel.INFO, "客户端[{}]结果分析:完成SQL次数:{},删除线程个数:{},删除总耗时:{}ms,平均耗时:{}ms,本客户端每秒完成删除次数:{}",System.getProperty("user.name"),
				DELETE_SQL_NUM, DELETE_THREAD_NUM, costtime, costtime
						/DELETE_SQL_NUM,DELETE_SQL_NUM*1000D/costtime);
	}

	/**
	 * 随机方式产生记录Id,多客户端运行冲突的概率约万分之几
	 * @return
	 */
	protected static long getStartId() throws SQLException {
		Connection conn = null;
		Statement st = null;
		ResultSet rs = null;
		try {
			conn = getConnection();
			st = conn.createStatement();
			st.setQueryTimeout(0);
			rs = st.executeQuery(min_sql);
			rs.next();
			return rs.getLong(1);
		} finally {
			close(conn);
			close(st);
			close(rs);
		}
	}

	public static int getCountNum() throws Exception{
		Connection conn = null;
		Statement st = null;
		ResultSet rs = null;
		try {
			conn = getConnection();
			st = conn.createStatement();
			st.setQueryTimeout(0);
			rs = st.executeQuery(sql);
			rs.next();
			return rs.getInt(1);
		} finally {
			close(conn);
			close(st);
			close(rs);
		}
	}

	private static void init() throws Exception{
		startId = getStartId();
		countNum = getCountNum();
		Connection conn = null;
		try{
			conn = getConnection();
		}finally{
			close(conn);
		}
	}

	protected static Connection getConnection() throws SQLException {
		return DriverManager.getConnection(url, user, password);
	}

	/**
	 * 输出线程(中间输出仅供估计，最终结果以主线程统计值为准)
	 * @author yancheng11334
	 *
	 */
	static class PrintThread extends AbstractProcessor {

		public PrintThread(String name) {
			super(name);
		}

		@Override
		protected void action() throws Exception {
			while (deleteCount.get() < DELETE_SQL_NUM) {

				long costtime = System.currentTimeMillis() - startTime;
				Connection conn = null;
				Statement st = null;
				long count = 0;
				try {
					//测试长连接(1个线程生命周期只创建一次连接)
					conn = getConnection();
					st =  conn.createStatement();
					ResultSet rs = st.executeQuery("select count(0) from score");
					if (rs.next()) {
						count = rs.getLong(1);
					}
				} finally {
					close(conn);
					close(st);
				}
				logger.logMessage(LogLevel.INFO, "所有客户端删除进度:完成SQL次数:{},删除线程个数:{},删除总耗时:{}ms,平均耗时:{}ms,本客户端每秒完成删除次数:{}",
						count, DELETE_THREAD_NUM, costtime, costtime
								/ (count==0?1:count),count*1000D/costtime);
				logger.logMessage(LogLevel.INFO, "本客户端删除进度:完成SQL次数:{},删除线程个数:{},删除总耗时:{}ms,平均耗时:{}ms,本客户端每秒完成删除次数:{}",
						deleteCount.get(), DELETE_THREAD_NUM, costtime, costtime
								/ (deleteCount.get()==0?1:deleteCount.get()),deleteCount.get()*1000D/costtime);

				Thread.sleep(PRINT_SLEEP_TIME);
			}
		}

	}

	/**
	 * 删除线程
	 * @author yancheng11334
	 *
	 */
	static class DeleteThread extends AbstractProcessor {
		private int count = 0;
		public DeleteThread(String name) {
			super(name);
		}

		@Override
		protected void action() throws Exception {
			logger.logMessage(LogLevel.INFO, "{}线程开始执行删除SQL...", getName());
			if(deleteByStatement){
				deleteByStatement();
			}else{
				deleteByPreparedStatement();
			}
			if(endTime==0){
				endTime = System.currentTimeMillis();
			}
			logger.logMessage(LogLevel.INFO, "{}线程删除完毕,完成SQL次数:{}", getName(),
					count);
		}

		private void deleteByPreparedStatement() throws Exception {
			Connection conn = null;
			PreparedStatement pStmt = null;
			try {
				//测试长连接(1个线程生命周期只创建一次连接)
				conn = getConnection();
				conn.setAutoCommit(false);
				pStmt = conn.prepareStatement("delete from score where id=?");
				pStmt.setQueryTimeout(0);
				int r=0;
				int sqlNum = DELETE_SQL_NUM;
				if(countNum<sqlNum) sqlNum=countNum;
				while ((r = deleteCount.getAndIncrement()) < sqlNum) {
					long id = startId+r;
					count++;
//					System.out.println("id="+id);
					pStmt.setLong(1, id);
					pStmt.addBatch();
					if(count%1000==0){
						pStmt.executeBatch();
					}
				}
				pStmt.executeBatch();
				conn.commit();
			} finally {
				close(conn);
				close(pStmt);
			}
		}

		/*public static void main(String[] args) throws SQLException {
			Connection conn = null;
			PreparedStatement pStmt = null;
			try {
				//测试长连接(1个线程生命周期只创建一次连接)
				conn = getConnection();
				conn.setAutoCommit(true);
				pStmt = conn.prepareStatement("delete from score where id=1");
				pStmt.executeUpdate();
			} finally {
				close(conn);
				close(pStmt);
			}
		}*/

		private void deleteByStatement() throws Exception {
			Connection conn = null;
			Statement st = null;
			try {
				//测试长连接(1个线程生命周期只创建一次连接)
				conn = getConnection();
				conn.setAutoCommit(autoCommit);
				st = conn.createStatement();
				st.setQueryTimeout(0);
				int r=0;
				int sqlNum = DELETE_SQL_NUM;
				if(countNum<sqlNum) sqlNum=countNum;
				while ((r=deleteCount.getAndIncrement()) < sqlNum) {
					long id = startId+r;
					count++;
					st.executeUpdate("delete from score where id="+id);
				}
				conn.commit();

				//测试短连接(1个线程每次抢到时间段，创建一个连接)
//				int r=0;
//				while ((r=deleteCount.getAndIncrement()) < DELETE_SQL_NUM) {
//					long id = startId+r;
//					count++;
//					conn = getConnection();
//					st = conn.createStatement();
//					st.executeUpdate("insert into score (id,name,score,course) values("+id+",'yancheng',60,'english')");
//					close(conn);
//					close(st);
//				}
			} finally {
				close(conn);
				close(st);
			}
		}

	}

}
